/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package NF;

import java.io.*;
import java.sql.*;

/**
 *
 * @author Joe Dombrowski
 */

public class Database 
{    
    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    BufferedWriter trace = null; // Trace
	
	int totalresult,noDupResult;
    
    // Always leve the connection parameters on top! and just in your main class!
    // Make sure to change the username, password, and last part of URL
    static String database           = "Team9";
    static String username           = "team9";
    static String password           = "njuu68os";
    static String server             = "129.7.243.245";
    static String port 	             = "1025";
    static String url                = "";
    static String integratedsecurity = "false";
    
    Database () throws Exception
    {
        try{
            trace = new BufferedWriter(new FileWriter("NF_team9.sql"));
        }catch(Exception e){ throw e; }
    }

    public String startConnection (String configFile) throws Exception
    {
        // Load config from file
        String[] test = new String[5];
        int i=0;

        try
        {
            FileInputStream fstream = new FileInputStream("config.jdbc");

            DataInputStream in = new DataInputStream(fstream);
            BufferedReader br = new BufferedReader(new InputStreamReader(in));
            String strLine;

            while ((strLine = br.readLine()) != null)   
            {
                String[] tokens;
                tokens = strLine.split("=");

                if(tokens.length >= 2)
                    test[i] = tokens[1];
                else
                    throw new Exception("Incorrect input file format, attempting default connection.");
                i++;
            }

            if(i != 5)
                throw new Exception("Config file does not have the correct number of lines, attempting default connection.");

            server=test[0];
            port=test[1];
            username=test[2];
            password=test[3];
            database=test[4];
            
            /*integratedSecurity is set to true when connecting through windows authentication ,else set it to false so that it uses the user name & pwd specified */
            if(server.compareTo("127.0.0.1") == 0 || server.compareTo("localhost") == 0)
                integratedsecurity = "true";
            
            url = "jdbc:sqlserver://"+server+":"+port+";databaseName="+database+";"+"integratedSecurity="+integratedsecurity+";";

            System.out.println ("Connecting to database \"" + database + "\"");

            in.close();
        }catch (Exception e) {  System.err.println("Error parsing file: " + e.getMessage());  }

        try
        {
            Class.forName ("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            System.out.print ("Starting database connection: ");
            conn = DriverManager.getConnection(url,username,password);
            System.out.println ("SUCCESS");
            stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        } catch (Exception e) {  throw new Exception(e);  }
        
        return "";
    }

    public boolean sendQuery (String query , int type) throws Exception
    {
        if (conn == null)
            return false;

        try 
        {
            if(type==1) /* 1 for select stmt */
            {
                trace.write(query); trace.newLine();
                rs = stmt.executeQuery(query);
            }
            else /* 0 for non-select stmt */
            {
                trace.write(query); trace.newLine();
                stmt.executeUpdate(query);
            }

        } catch (Exception e) {  throw new Exception(e);  }
        
        return true;
    }
    
    public boolean sendQuery (String query) throws Exception
    {
        if (conn == null)
            return false;

        int type = 0;
        if(query.toLowerCase().contains("select"))
            type = 1;
        
        try 
        {
            if(type==1) /* 1 for select stmt */
            {
                trace.write(query); trace.newLine();
                rs = stmt.executeQuery(query);
            }
            else /* 0 for non-select stmt */
            {
                trace.write(query); trace.newLine();
                stmt.executeUpdate(query);
            }
        } catch (Exception e) {  throw new Exception(e);  }
        
        return true;
    }

    public String[] getResult (String[] colNames) throws SQLException
    {
        String[] results = new String[0];

        if (rs == null)
        {
            System.out.println ("ResultSet is null");
            return results;
        }

        try
        {
            if (rs.next ())
            {
                results = new String[colNames.length];
                for (int i = 0; i < colNames.length; i++)
                results[i] = rs.getString (colNames[i]);
            }
            else
                return results;
        } catch (SQLException e) { throw new SQLException(e); }
        
        return results;
    }

    public String stopConnection ()
    {
        if (conn == null)
            return "Database not connected";

        System.out.print ("Stopping database connection: ");
        if (rs != null) 
        {
            try 
            {
                rs.close();
            } catch (SQLException sqlEx) { }
            
            rs = null;
        }

        if (stmt != null) 
        {
            try 
            {
                stmt.close();
            } catch (SQLException sqlEx) { }
            
            stmt = null;
        }
        if (trace != null) 
        {
            try 
            {
                trace.write("SELECT * FROM NF_team9"); trace.newLine();
                trace.close();
            } catch (Exception e) { }
            
            trace = null;
        }

        try 
        {
            conn.close ();
        } catch (SQLException e) { };

        System.out.println ("SUCCESS");

        return "";
    }
    
    public String[] NFResult(String tableName, String[] candidateKeys, String[] otherColumns) throws Exception
    {
        String[] result;
        
        // Check if it's 1NF
        try{
            result = Check1NF(tableName, candidateKeys, otherColumns);
        
            // Null result indicates no errors
            if(result[1] == null)
            {
                // Check if it's 2NF
                result = Check2NF(tableName, candidateKeys, otherColumns);
                if(result[1] == null)
                {
                    // Check if it's 3NF
                    result = Check3NF(tableName, candidateKeys, otherColumns);
                }
            }
        }catch(Exception e){ throw e; }
        
        return result;
    }
    
    public String[] Check1NF(String tableName, String[] candidateKeys, String[] otherColumns) throws Exception
    {
        String[] result = {null,null};
        
        if(candidateKeys.length == 0)
        {
            result[1] = "Table has no candidate keys";
            return result;
        }
        
        String resultString = "";
        
        int numNulls = 0;
        boolean is1NF = true;
        try
        {
            // This is a string with each of the candidate Keys that checks for nulls
            String checkNull = "";
            for(int i=0; i<candidateKeys.length; i++)
            {
                checkNull += candidateKeys[i] + " IS NULL";
                if(i+1 != candidateKeys.length)
                    checkNull += " OR ";
            }
            
            // This statement looks for any null values in a candidate key.  
            // If there are any, it sets is1NF to false
            this.sendQuery("SELECT COUNT(*) FROM " + tableName + " WHERE " + checkNull);
            
            while(rs.next())
                numNulls = rs.getInt(1);
            is1NF = numNulls == 0;
            if(!is1NF)
                resultString += "Key contains NULL values";
        }catch(Exception e){ throw e; }
        
        // This block will check for any duplicate keys
        try
        {
            totalresult = -1; noDupResult = -1;

            // This retrieves the total number of rows in the table
            this.sendQuery("SELECT COUNT(*) FROM " + tableName);
            while(rs.next())
                totalresult = rs.getInt(1);

            // Formulate a string of form "K1,K2,K3..."
            if(candidateKeys.length == 0)
            {
                // Output an error that says the table has no candidate keys
            }
            else if(candidateKeys.length == 1)
            {
                this.sendQuery("SELECT COUNT(DISTINCT " + candidateKeys[0] + ") FROM " + tableName);
                while(rs.next())
                    noDupResult = rs.getInt(1);
            }
            else
            {
                String keyString = "";
                for(int i=0; i<candidateKeys.length; i++)
                {
                    keyString += candidateKeys[i];
                    if(i+1 != candidateKeys.length)
                        keyString += ",";
                }

                // This retrieves the number of distinct composite keys.
                this.sendQuery("SELECT COUNT(*) FROM (SELECT DISTINCT " + keyString + " FROM " + tableName + ") AS d1");
                while(rs.next())
                    noDupResult = rs.getInt(1);
            }
            
            if(totalresult - numNulls != noDupResult)
            {
                is1NF = false;
                if(resultString.compareTo("") != 0)
                    resultString += ", ";
                resultString += "Candidate key has duplicate values";
            }
        }catch(Exception e){ throw e; }
        
        if(is1NF)
            result[0] = "1NF";
        if(resultString.compareTo("") != 0)
            result[1] = resultString;
        
        return result;
    }
    
    public String[] Check2NF(String tableName, String[] candidateKeys, String[] otherColumns) throws Exception
    {
        String[] result = {"1NF",null};
        String resultString = "";
        
        
        
        if(candidateKeys.length == 1)
        {
            result[0] = "2NF";
            return result;
        }
        
        
        else
        {
            for(int cK = 0; cK < candidateKeys.length; cK++)
            {
                try
                {
                    // Count how many distinct values there are in the current key
                    int distinctCK = 0;
                    this.sendQuery("SELECT COUNT(DISTINCT "+candidateKeys[cK]+" ) from " + tableName);
                    while(rs.next())
                        distinctCK = rs.getInt(1);
                                    
                                  
                    // Check CK with all other CK's
                    for(int otherKey = cK+1; otherKey < candidateKeys.length; otherKey++)
                    {
                        int distinct_composite=0;
                        
                        this.sendQuery("SELECT COUNT(*) As Expr1 FROM (SELECT DISTINCT " +  candidateKeys[cK] + "," + candidateKeys[otherKey] +  
                                " FROM " + tableName +") As d1");
                        
                                           
                        while(rs.next())
                        {
                        	distinct_composite = rs.getInt(1);
                            
                        }
                        
                        
                        if(totalresult!=distinct_composite)
                        { 
                            
                        	if(resultString.compareTo("") != 0)
                                resultString += ", ";
                            resultString += candidateKeys[cK] + "->" + candidateKeys[cK+1];
                            
                        }
                    } 
                    
                    // Check this key against all the non-keys
                    for(int oC = 0; oC < otherColumns.length; oC++)
                    {   
                        int[] resultArray = new int[10];
                        int counter = 0;
                        
                        this.sendQuery("SELECT COUNT(*) AS Expr1 FROM (SELECT DISTINCT " + 
                                candidateKeys[cK] + "," + otherColumns[oC] +  
                                " FROM " + tableName + ") AS d1 GROUP BY " + candidateKeys[cK]);
                        
                        while(rs.next())
                        {
                            if(counter == resultArray.length)
                            {
                                int[] temp = new int[resultArray.length];
                                System.arraycopy(resultArray, 0, temp, 0, resultArray.length);
                                resultArray = new int[temp.length*2];
                                System.arraycopy(temp, 0, resultArray, 0, temp.length);
                            }
                            resultArray[counter] = rs.getInt(1);
                            counter++;
                        }
                        
                        counter = 0;
                        
                        for(int i=0; i<distinctCK; i++)
                            if(resultArray[i] > 1)
                                counter++;
                        
                        if(counter == 0)
                        {
                            if(resultString.compareTo("") != 0)
                                resultString += ", ";
                            resultString += candidateKeys[cK] + "->" + otherColumns[oC];
                        }
                    }
                }catch(Exception e){ throw e; }
            }
        }
        
        if(resultString.compareTo("") != 0)
        {
            result[0] = "1NF";
            result[1] = resultString;
        }
        else
        {
            result[0] = "2NF";
            result[1] = null;
        }
        return result;
    }
    
    private String[] Check3NF(String tableName, String[] candidateKeys, String[] otherColumns)
    {
        String[] result = {"2NF",null};
        String resultString = "";
        
        int[] OtherColumn1=new int[10],OtherColumn2=new int[10];
        int counter=0;
        
        //Compare every non-key with every other non-key.
       
        
        try 
        {
        	
        for(int oC1=0;oC1<otherColumns.length;oC1++)
        {
        	for(int oC2=oC1+1;oC2<otherColumns.length;oC2++)
        	{
        		this.sendQuery("SELECT COUNT(*) AS Expr1 FROM (SELECT DISTINCT " + 
        				otherColumns[oC1] + "," + otherColumns[oC2] +  
                                " FROM " + tableName + ") AS d1 GROUP BY " + otherColumns[oC1]);
        		
        		
        		 while(rs.next())
                 {
                     if(counter == OtherColumn1.length)
                     {
                         int[] temp1 = new int[OtherColumn1.length];
                         System.arraycopy(OtherColumn1, 0, temp1, 0, OtherColumn1.length);
                         OtherColumn1 = new int[temp1.length*2];
                         System.arraycopy(temp1, 0, OtherColumn1, 0, temp1.length);
                         
                     }
                     OtherColumn1[counter] = rs.getInt(1);
                     counter++;
                 }
        		
        		 
        		 this.sendQuery("SELECT COUNT(*) AS Expr1 FROM (SELECT DISTINCT " + 
         				otherColumns[oC2] + "," + otherColumns[oC1] +  
                                 " FROM " + tableName + ") AS d1 GROUP BY " + otherColumns[oC2]);
        		 
        		 while(rs.next())
                 {
                     if(counter == OtherColumn2.length)
                     {
                         int[] temp2 = new int[OtherColumn2.length];
                         System.arraycopy(OtherColumn2, 0, temp2, 0, OtherColumn2.length);
                         OtherColumn2 = new int[temp2.length*2];
                         System.arraycopy(temp2, 0, OtherColumn2, 0, temp2.length);
                     }
                     OtherColumn1[counter] = rs.getInt(1);
                     counter++;
                 }
        		      
        		 int counter1=0,counter2=0;
        		 
        		 for(int i=0;i<totalresult;i++)
        		 {
        			 if(OtherColumn1[i]>1)
        			     counter1++;
        			 if(OtherColumn2[i]>1) 
        				 counter2++;
        		 }
        		 
        		 if((counter1==0) && (counter2==0))
        		 {
        			 if(resultString.compareTo("") != 0)
                         resultString += ", ";
                     resultString += otherColumns[oC1] + "->" + otherColumns[oC2]+", "+otherColumns[oC2] + "->" + otherColumns[oC1];
        		 }
        	}
        }
        
        
        
        
    }catch(Exception e){};
    
    if(resultString.compareTo("") != 0)
    {
        result[0] = "2NF";
        result[1] = resultString;
    }
    else
    {
        result[0] = "3NF";
        result[1] = null;
    }
        return result;
    }
}
